package com.huaxia.dao.realAct;


import com.huaxia.pojo.realAct.BranchMonthAct;

import com.huaxia.pojo.realAct.Month10000;
import org.apache.ibatis.annotations.Select;


import java.util.List;
import java.util.Map;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/08 16:23
 */

public interface BranchMonthActMapper {

    @Select("select x.BRANCHSNAME,x.branchMonAdvance5000,x.branchMonAdvance10000,y.branchMonInSure5000,y.branchMonInSure10000 from          \n" +
            "            (\n" +
            "                    select t.branchsname,sum(t.branchMonAdvance5000)branchMonAdvance5000,sum(t.branchMonAdvance10000)branchMonAdvance10000 from(\n" +
            "                    select sb.branchsname,NVL(a.a,0)branchMonAdvance5000,NVL(a.c,0)branchMonAdvance10000 from           \n" +
            "                    (select x.saledeptcode,SUM(case when x.mys>='5000' then 1 end)a,SUM(case when x.mys>='10000' then 1 end)c from            \n" +
            "                    (select t.saledeptcode, SUM(t.PRESTADPREM)mys from FACT_YX_PREPREM_LIST_day t where \n" +
            "                    ((TO_CHAR(RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null)) or\n" +
            "                    (TO_CHAR(RECEIVE_DATE,'YYYY/MM') = TO_char(add_months(trunc(sysdate),-1),'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N')) AND t.RNFLAG='N' and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND t.Agentstate='在职' group by t.agentcode,t.saledeptcode)x group by x.saledeptcode)a \n" +
            "                    right join sd_branch2 sb on sb.saledeptcode=a.saledeptcode\n" +
            "                    )t group by t.branchsname\n" +
            "            )x, \n" +
            "            \n" +
            "            (\n" +
            "                    select t.branchsname,sum(t.branchMonInSure5000)branchMonInSure5000,sum(t.branchMonInSure10000)branchMonInSure10000 from(\n" +
            "                    select sb.branchsname,NVL(a.a,0)branchMonInSure5000,NVL(a.c,0)branchMonInSure10000 from  \n" +
            "                    (select x.saledeptcode,SUM(case when x.YCB>='5000' then 1 end)a,SUM(case when x.YCB>='10000' then 1 end)c FROM\n" +
            "                    ( select a.saledeptcode,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "                    (select t.saledeptcode, t.AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t \n" +
            "                    where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and t.RNFLAG='N' and t.AGENTSTATE='在职' \n" +
            "                    group by t.AGENTCODE,t.saledeptcode)a left join \n" +
            "                    (select s.saledeptcode, s.AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s \n" +
            "                    where TO_CHAR(s.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and s.RNFLAG='N' and s.AGENTSTATE='在职' \n" +
            "                    group by s.AGENTCODE,s.saledeptcode)b on a.AGENTCODE=b.AGENTCODE)x GROUP BY x.saledeptcode)a right join sd_branch2 sb on sb.saledeptcode=a.saledeptcode\n" +
            "                    )t group by t.branchsname\n" +
            "            )y \n" +
            "            where x.BRANCHSNAME=y.BRANCHSNAME order by y.branchMonInSure10000 desc")
    List<BranchMonthAct> getBranchThisMonthAct();


    @Select("select t.branchsname,sum(t.monAdd10000)monAdd10000 from(\n" +
            "        select sb.branchsName,nvl(a.monAdd10000,0)monAdd10000 from  sd_branch2 sb left join (\n" +
            "        select x.saledeptcode,SUM(case when x.mys>='10000' then 1 end)monAdd10000 from\n" +
            "        (\n" +
            "        select t.saledeptcode, SUM(t.PRESTADPREM)mys from FACT_YX_PREPREM_LIST_day t where\n" +
            "        TO_CHAR(t.RECEIVE_DATE,'YYYY/MM/DD')=TO_CHAR(SYSDATE,'YYYY/MM/DD') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null) AND t.RNFLAG='N' and\n" +
            "        (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND t.Agentstate='在职'\n" +
            "        group by t.agentcode,t.saledeptcode\n" +
            "        )x group by x.saledeptcode\n" +
            "        )a on sb.saledeptcode=a.saledeptcode\n" +
            "        )t group by t.branchsname")
    List<Month10000> findMonAdd10000_1();

    @Select("select t.branchsname,sum(t.monAdd10000)monAdd10000 from(\n" +
            "    select sb.branchsName,nvl(a.monAdd10000,0)monAdd10000 from  sd_branch2 sb left join (\n" +
            "            select x.saledeptcode,SUM(case when x.mys>='10000' then 1 end)monAdd10000 from            \n" +
            "                           (\n" +
            "            select t.saledeptcode, SUM(t.PRESTADPREM)mys from FACT_YX_PREPREM_LIST_day t where \n" +
            "                        ((TO_CHAR(RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null)) or\n" +
            "            (TO_CHAR(RECEIVE_DATE,'YYYY/MM') = TO_char(add_months(trunc(sysdate),-1),'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N')) AND t.RNFLAG='N' and \n" +
            "            (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND t.Agentstate='在职'\n" +
            "             group by t.agentcode,t.saledeptcode\n" +
            "             )x group by x.saledeptcode\n" +
            "            )a on sb.saledeptcode=a.saledeptcode\n" +
            "            )t group by t.branchsname")
    List<Month10000> findMonAdd10000_before15_includeToday();

    
    @Select("select t.branchsname,sum(t.monAdd10000)monAdd10000 from(\n" +
            "    select sb.branchsName,nvl(a.monAdd10000,0)monAdd10000 from  sd_branch2 sb left join (\n" +
            "            select x.saledeptcode,SUM(case when x.mys>='10000' then 1 end)monAdd10000 from            \n" +
            "                           (\n" +
            "            select t.saledeptcode, SUM(t.PRESTADPREM)mys from FACT_YX_PREPREM_LIST_day t where \n" +
            "                        ((TO_CHAR(RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null)) or\n" +
            "            (TO_CHAR(RECEIVE_DATE,'YYYY/MM') = TO_char(add_months(trunc(sysdate),-1),'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N')) and\n" +
            "            TO_CHAR(t.RECEIVE_DATE,'YYYY/MM/DD')!=TO_CHAR(SYSDATE,'YYYY/MM/DD')  AND t.RNFLAG='N' and \n" +
            "            (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND t.Agentstate='在职'\n" +
            "             group by t.agentcode,t.saledeptcode\n" +
            "             )x group by x.saledeptcode\n" +
            "            )a on sb.saledeptcode=a.saledeptcode\n" +
            "            )t group by t.branchsname")
    List<Month10000> findMonAdd10000_before15_noneToday();


    @Select("select t.branchsname,sum(t.monAdd10000)monAdd10000 from(\n" +
            "    select sb.branchsname,NVL(a.c,0)monAdd10000 from \n" +
            "             \n" +
            "                        (select x.saledeptcode,SUM(case when x.YCB>='10000' then 1 end)c FROM\n" +
            "                        ( select a.saledeptcode,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "                                    (select t.saledeptcode, t.AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t \n" +
            "                                    where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and t.RNFLAG='N' and t.AGENTSTATE='在职' \n" +
            "                                    group by t.AGENTCODE,t.saledeptcode)a left join \n" +
            "                                    (select s.saledeptcode, s.AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s \n" +
            "                                    where TO_CHAR(s.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and s.RNFLAG='N' and s.AGENTSTATE='在职' \n" +
            "                                    group by s.AGENTCODE,s.saledeptcode)b on a.AGENTCODE=b.AGENTCODE)x \n" +
            "            \n" +
            "                           GROUP BY x.saledeptcode)a right join sd_branch2 sb on sb.saledeptcode=a.saledeptcode\n" +
            "                     )t group by t.branchsname")
    List<Month10000> findMonAdd10000_after15_includeToday();


    

    @Select("select t.branchsname,sum(t.monAdd10000)monAdd10000 from(\n" +
            "    select sb.branchsname,NVL(a.c,0)monAdd10000 from \n" +
            "             \n" +
            "                        (select x.saledeptcode,SUM(case when x.YCB>='10000' then 1 end)c FROM\n" +
            "                        ( select a.saledeptcode,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "                                    (select t.saledeptcode, t.AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t \n" +
            "                                    where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(t.ISSUE_DATE,'YYYY/MM/DD')!=TO_CHAR(SYSDATE,'YYYY/MM/DD') and t.RNFLAG='N' and t.AGENTSTATE='在职' \n" +
            "                                    group by t.AGENTCODE,t.saledeptcode)a left join \n" +
            "                                    (select s.saledeptcode, s.AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s \n" +
            "                                    where TO_CHAR(s.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and TO_CHAR(s.YT_DATE,'YYYY/MM/DD')!=TO_CHAR(SYSDATE,'YYYY/MM/DD') and s.RNFLAG='N' and s.AGENTSTATE='在职' \n" +
            "                                    group by s.AGENTCODE,s.saledeptcode)b on a.AGENTCODE=b.AGENTCODE)x \n" +
            "            \n" +
            "                           GROUP BY x.saledeptcode)a right join sd_branch2 sb on sb.saledeptcode=a.saledeptcode\n" +
            "                     )t group by t.branchsname")
    List<Month10000> findMonAdd10000_after15_noneToday();


    //次月


    @Select("select x.BRANCHSNAME,x.branchMonAdvance5000,x.branchMonAdvance10000,y.branchMonInSure5000,y.branchMonInSure10000 from\n" +
            "            (\n" +
            "                    select t.branchsname,sum(t.branchMonAdvance5000)branchMonAdvance5000,sum(t.branchMonAdvance10000)branchMonAdvance10000 from(\n" +
            "                    select sb.branchsname,NVL(a.a,0)branchMonAdvance5000,NVL(a.c,0)branchMonAdvance10000 from\n" +
            "                    (select x.saledeptcode,SUM(case when x.mys>='5000' then 1 end)a,SUM(case when x.mys>='10000' then 1 end)c from\n" +
            "                    (select t.saledeptcode, SUM(t.PRESTADPREM)mys from FACT_YX_PREPREM_LIST_day t where\n" +
            "                    ((TO_CHAR(t.RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N') or (TO_CHAR(t.RECEIVE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),1),'YYYY/MM'))) AND t.RNFLAG='N' and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND t.Agentstate='在职' group by t.agentcode,t.saledeptcode)x group by x.saledeptcode)a\n" +
            "                    right join sd_branch2 sb on sb.saledeptcode=a.saledeptcode\n" +
            "                    )t group by t.branchsname\n" +
            "            )x,\n" +
            "            (\n" +
            "                    select t.branchsname,sum(t.branchMonInSure5000)branchMonInSure5000,sum(t.branchMonInSure10000)branchMonInSure10000 from(\n" +
            "                    select sb.branchsname,NVL(a.a,0)branchMonInSure5000,NVL(a.c,0)branchMonInSure10000 from\n" +
            "                    (select x.saledeptcode,SUM(case when x.YCB>='5000' then 1 end)a,SUM(case when x.YCB>='10000' then 1 end)c FROM\n" +
            "                    ( select a.saledeptcode,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "                        (select t.saledeptcode, t.AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t \n" +
            "                        where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),1),'YYYY/MM') and t.RNFLAG='N' and t.AGENTSTATE='在职' \n" +
            "                        group by t.AGENTCODE,t.saledeptcode)a left join \n" +
            "                        (select s.saledeptcode, s.AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s \n" +
            "                        where TO_CHAR(s.YT_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),1),'YYYY/MM') and s.RNFLAG='N' and s.AGENTSTATE='在职' \n" +
            "                        group by s.AGENTCODE,s.saledeptcode)b on a.AGENTCODE=b.AGENTCODE)x GROUP BY x.saledeptcode)a right join sd_branch2 sb on sb.saledeptcode=a.saledeptcode\n" +
            "                     )t group by t.branchsname\n" +
            "            )y\n" +
            "            where x.BRANCHSNAME=y.BRANCHSNAME order by x.branchMonAdvance10000 desc")
    List<BranchMonthAct> getBranchNextMonthAct();



    


    @Select("select t.branchsname,sum(t.monAdd10000)monAdd10000 from(\n" +
            "    select sb.branchsName,nvl(a.monAdd10000,0)monAdd10000 from  sd_branch2 sb left join (\n" +
            "            select x.saledeptcode,SUM(case when x.mys>='10000' then 1 end)monAdd10000 from            \n" +
            "                           (\n" +
            "            select t.saledeptcode, SUM(t.PRESTADPREM)mys from FACT_YX_PREPREM_LIST_day t where \n" +
            "                        ((TO_CHAR(t.RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N') or (TO_CHAR(t.RECEIVE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),1),'YYYY/MM'))) AND t.RNFLAG='N' and \n" +
            "            (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND t.Agentstate='在职'\n" +
            "             group by t.agentcode,t.saledeptcode\n" +
            "             )x group by x.saledeptcode\n" +
            "            )a on sb.saledeptcode=a.saledeptcode\n" +
            "     )t group by t.branchsname")
    List<Month10000> findNextMonAdd10000_includeToday();


    
    
    @Select("select t.branchsname,sum(t.monAdd10000)monAdd10000 from(\n" +
            "    select sb.branchsName,nvl(a.monAdd10000,0)monAdd10000 from  sd_branch2 sb left join (\n" +
            "            select x.saledeptcode,SUM(case when x.mys>='10000' then 1 end)monAdd10000 from            \n" +
            "                           (\n" +
            "            select t.saledeptcode, SUM(t.PRESTADPREM)mys from FACT_YX_PREPREM_LIST_day t where \n" +
            "                        ((TO_CHAR(t.RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N') or (TO_CHAR(t.RECEIVE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),1),'YYYY/MM'))) and\n" +
            "             t.RNFLAG='N' and TO_CHAR(t.receive_DATE,'YYYY/MM/DD')!=TO_CHAR(SYSDATE,'YYYY/MM/DD') and\n" +
            "            (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND t.Agentstate='在职'\n" +
            "             group by t.agentcode,t.saledeptcode\n" +
            "             )x group by x.saledeptcode\n" +
            "            )a on sb.saledeptcode=a.saledeptcode\n" +
            "     )t group by t.branchsname")
    List<Month10000> findNextMonAdd10000_noneToday();
    
}

    